import java.io.*;
import javax.servlet.http.*;
import javax.servlet.*;
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

public class Teams extends HttpServlet
{

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {

        response.setContentType("text/html");
        PrintWriter out = response.getWriter();

        String dbUser = "jh3240"; // enter your username here
        String dbPassword = "kuzifQbw"; // enter your password here

        try
        {
            OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
            ods.setURL("jdbc:oracle:thin:@//w4111g.cs.columbia.edu:1521/ADB");
            ods.setUser(dbUser);
            ods.setPassword(dbPassword);

            Connection conn = ods.getConnection();

            String query = new String();
            Statement s = conn.createStatement();

            String teamid = request.getParameter("teamid");
            String year = request.getParameter("year");
            String query_date = year + "-01-02";

            query = "select t.name as tname, t.manager, s.name as sname, s.country, s.state, s.city "
                    + "from teams t, stadiums s, owned_by o where t.tid = o.tid and s.sid = o.sid and t.tid = '"
                    + teamid + "'";

            String tname = "";
            ResultSet r = s.executeQuery(query);
            int i = 0;
            while (r.next())
            {
                tname = r.getString("tname");
                out.println("<html><head><title>" + tname + " in " + year + "</title></head>");
                out.println("<body>");
                out.println("<h1>" + tname + " in " + year + "</h1>");
                out.println("<h2>Manager: " + r.getString("manager") + "</h2>");
                out.println("<h2>Stadium: " + r.getString("sname") + "</h2>");
                out.println("<h2>Location: " + r.getString("city") + ", " + r.getString("state") + ", " + r.getString("country") + "</h2>");
            }
            r.close();

            query = "SELECT p.name, p.playerid FROM plays_for pl, players p WHERE p.playerid = pl.playerid AND pl.tid = '" + teamid
                    + "' AND pl.since < TO_DATE('" + query_date + "', 'YYYY-MM-DD') AND pl.until > TO_DATE('"
                    + query_date + "', 'YYYY-MM-DD')";

            r = s.executeQuery(query);

            out.println("<h2>Players of " + tname + " in " + year + "</h2>");
            while (r.next())
            {
                out.println("<a href=\"/player?pid=" + r.getString("playerid") + "\">" + r.getString("name") + "</a><br />");
            }
            r.close();

            query = "SELECT ht.name as hname, at.name as aname, at.tid as atid, ht.tid as htid, g.homescore, g.awayscore, g.gid, g.played_on "
                    + "FROM games g, teams ht, teams at "
                    + "WHERE (ht.tid = '"
                    + teamid
                    + "' OR at.tid = '"
                    + teamid
                    + "') AND ht.tid = g.hometeam and at.tid = g.awayteam and TO_CHAR(g.played_on, 'YYYY') = '"
                    + year
                    + "' ORDER BY g.played_on";
            r = s.executeQuery(query);

            out.println("<h2>Games of " + tname + " in " + year + "</h2>");
            while (r.next())
            {
                String scoreStr = r.getString("awayscore") + " to " + r.getString("homescore");
                String scoreLink = "<a href=\"/game?gid=" + r.getString("gid") + "\">" + scoreStr + "</a>";
                Date played_on = r.getDate("played_on");
                out.println(played_on.toString() + ": <a href=\"/team?teamid=" + r.getString("atid") + "&year=" + year
                        + "\">" + r.getString("aname") + "</a>" + " at " + "<a href=\"/team?teamid="
                        + r.getString("htid") + "&year=" + year + "\">" + r.getString("hname") + "</a>"
                        + " with score " + scoreLink + "<br />");
            }
            r.close();

            out.println("</body></html>");
            
            s.close();
            conn.close();

        }
        catch (Exception e)
        {
            out.println("The database could not be accessed.<br>");
            out.println("More information is available as follows:<br>");
            e.printStackTrace(out);
        }

    } // end doGet method

} // end DatabaseServlet class

